package com.four.peoplemanage.information.dao;
import com.four.peoplemanage.information.entity.Information;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface InformationDao {
    // 查看所有员工（合同工）
    @Select("<script>" +
            "SELECT * FROM traffic.information" +
            "<where>" +
            "    <if test=\"name != null\">" +
            " <bind name=\"Key\" value=\"'%'+name+'%'\"/>" +
            "        name like #{Key} " +
            "    </if>" +
            "    <if test=\"number != null\">" +
            "      and number = #{number}" +
            "   </if>" +
            "    <if test=\"sex != null\">" +
            "      and sex = #{sex}" +
            "   </if>" +
            "    <if test=\"employee != null\">" +
            "      and employee = #{employee}" +
            "   </if>" +
            "    <if test=\"grade != null\">" +
            "      and grade = #{grade}" +
            "   </if>" +
            "    <if test=\"duties != null\">" +
            "      and duties = #{duties}" +
            "   </if>" +
            "    <if test=\"education != null\">" +
            "      and education = #{education}" +
            "   </if>" +
            "    <if test=\"marriage != null\">" +
            "      and marriage = #{marriage}" +
            "   </if>" +
            "   and employee = '合同工'"+
            "</where>" +
            "limit #{start}, #{size}" +
            "</script>")
    List<Information> canInformation(int start, int size,
                                     String name, Integer number,
                                     String sex, String employee,
                                     String grade, String duties,
                                     String education, String marriage);
    //分页
    @Select("<script>" +
            "SELECT count(*) FROM traffic.information" +
            "<where>" +
            "    <if test=\"name != null\">" +
            " <bind name=\"Key\" value=\"'%'+name+'%'\"/>" +
            "        name like #{Key} " +
            "    </if>" +
            "    <if test=\"number != null\">" +
            "      and number = #{number}" +
            "   </if>" +
            "    <if test=\"sex != null\">" +
            "      and sex = #{sex}" +
            "   </if>" +
            "    <if test=\"employee != null\">" +
            "      and employee = #{employee}" +
            "   </if>" +
            "    <if test=\"grade != null\">" +
            "      and grade = #{grade}" +
            "   </if>" +
            "    <if test=\"duties != null\">" +
            "      and duties = #{duties}" +
            "   </if>" +
            "    <if test=\"education != null\">" +
            "      and education = #{education}" +
            "   </if>" +
            "    <if test=\"marriage != null\">" +
            "      and marriage = #{marriage}" +
            "   </if>" +
            "   and employee = '合同工'"+
            "</where>" +
            "</script>")
    public int countInformation(String name, Integer number,
                                String sex, String employee,
                                String grade, String duties,
                                String education, String marriage);

    // 查看所有员工（外聘）
    @Select("<script>" +
            "SELECT * FROM traffic.information" +
            "<where>" +
            "    <if test=\"name != null\">" +
            " <bind name=\"Key\" value=\"'%'+name+'%'\"/>" +
            "        name like #{Key} " +
            "    </if>" +
            "    <if test=\"number != null\">" +
            "      and number = #{number}" +
            "   </if>" +
            "    <if test=\"sex != null\">" +
            "      and sex = #{sex}" +
            "   </if>" +
            "    <if test=\"employee != null\">" +
            "      and employee = #{employee}" +
            "   </if>" +
            "    <if test=\"grade != null\">" +
            "      and grade = #{grade}" +
            "   </if>" +
            "    <if test=\"duties != null\">" +
            "      and duties = #{duties}" +
            "   </if>" +
            "    <if test=\"education != null\">" +
            "      and education = #{education}" +
            "   </if>" +
            "    <if test=\"marriage != null\">" +
            "      and marriage = #{marriage}" +
            "   </if>" +
            "   and employee = '外聘工'"+
            "</where>" +
            "limit #{start}, #{size}" +
            "</script>")
    List<Information> canInformation1(int start, int size,
                                     String name, Integer number,
                                     String sex, String employee,
                                     String grade, String duties,
                                     String education, String marriage);
    //分页
    @Select("<script>" +
            "SELECT count(*) FROM traffic.information" +
            "<where>" +
            "    <if test=\"name != null\">" +
            " <bind name=\"Key\" value=\"'%'+name+'%'\"/>" +
            "        name like #{Key} " +
            "    </if>" +
            "    <if test=\"number != null\">" +
            "      and number = #{number}" +
            "   </if>" +
            "    <if test=\"sex != null\">" +
            "      and sex = #{sex}" +
            "   </if>" +
            "    <if test=\"employee != null\">" +
            "      and employee = #{employee}" +
            "   </if>" +
            "    <if test=\"grade != null\">" +
            "      and grade = #{grade}" +
            "   </if>" +
            "    <if test=\"duties != null\">" +
            "      and duties = #{duties}" +
            "   </if>" +
            "    <if test=\"education != null\">" +
            "      and education = #{education}" +
            "   </if>" +
            "    <if test=\"marriage != null\">" +
            "      and marriage = #{marriage}" +
            "   </if>" +
            "   and employee = '外聘工'"+
            "</where>" +
            "</script>")
    public int countInformation1(String name, Integer number,
                                String sex, String employee,
                                String grade, String duties,
                                String education, String marriage);

    //查看所有
    @Select("select * from information where employee = '合同工' order by id asc")
    List<Information> selectAll();

    // 插入新的员工信息
    @Insert("INSERT INTO traffic.information (number, name, identity, sex, birth, grade, employee,compile, role, jobtitle, department, education, duties, title, birthplace, nation, party_time, entry_date, political_outlook, education_type, fulltimedegree, fulldegree, fulltimeprofession, jobeducation, jobdegree, jobprofession, phonenumber, marriage) " +
            "VALUES (#{number}, #{name}, #{identity}, #{sex}, #{birth}, #{grade}, #{employee},#{compile}, #{role}, #{jobtitle}, #{department}, #{education}, #{duties}, #{title}, #{birthplace}, #{nation}, #{party_time}, #{entry_date}, #{political_outlook}, #{education_type}, #{fulltimedegree}, #{fulldegree}, #{fulltimeprofession}, #{jobeducation}, #{jobdegree}, #{jobprofession}, #{phonenumber}, #{marriage})")
    int insertInformation(Information information);

    //批量插入
        @Insert("<script>" +
                "INSERT INTO traffic.information (number, name, identity, sex, birth, grade, employee, role, jobtitle, department, education, duties, title, birthplace, nation, party_time, entry_date, political_outlook, education_type, fulltimedegree, fulldegree, fulltimeprofession, jobeducation, jobdegree, jobprofession, phonenumber, marriage) " +
                "VALUES " +
                "<foreach collection='list' item='item' index='index' separator=','>" +
                "(#{item.number}, #{item.name}, #{item.identity}, #{item.sex}, #{item.birth}, #{item.grade}, #{item.employee}, #{item.role}, #{item.jobtitle}, #{item.department}, #{item.education}, #{item.duties}, #{item.title}, #{item.birthplace}, #{item.nation}, #{item.party_time}, #{item.entry_date}, #{item.political_outlook}, #{item.education_type}, #{item.fulltimedegree}, #{item.fulldegree}, #{item.fulltimeprofession}, #{item.jobeducation}, #{item.jobdegree}, #{item.jobprofession}, #{item.phonenumber}, #{item.marriage})" +
                "</foreach>" +
                "</script>")
        int insert(List<Information> informationList);


    //修改员工状态
    @Update("UPDATE traffic.information SET status = #{status} WHERE id = #{id}")
    int updatestatus(@Param("id") Integer id,@Param("status") Integer status);

    //修改信息
    @Update("<script>" +
            "  update information" +
            "        <set>" +
            "            <if test=\"null != name\"> " +
            "                name = #{name}," +
            "            </if>" +
            "            <if test=\"null != grade\"> " +
            "                grade = #{grade}," +
            "            </if>" +
            "            <if test=\"null != jobtitle\"> " +
            "                jobtitle = #{jobtitle}," +
            "            </if>" +
            "            <if test=\"null != education\"> " +
            "                education = #{education}," +
            "            </if>" +
            "            <if test=\"null != duties\"> " +
            "                duties = #{duties}," +
            "            </if>" +
            "            <if test=\"null != marriage\"> " +
            "                marriage = #{marriage}," +
            "            </if>" +
            "            <if test=\"null != fulltimedegree\"> " +
            "                fulltimedegree = #{fulltimedegree}," +
            "            </if>" +
            "            <if test=\"null != fulldegree\"> " +
            "                fulldegree = #{fulldegree}," +
            "            </if>" +
            "            <if test=\"null != fulltimeprofession\"> " +
            "                fulltimeprofession = #{fulltimeprofession}," +
            "            </if>" +
            "            <if test=\"null != jobeducation\"> " +
            "                jobeducation = #{jobeducation}," +
            "            </if>" +
            "            <if test=\"null != jobdegree\"> " +
            "                jobdegree = #{jobdegree}," +
            "            </if>" +
            "            <if test=\"null != jobprofession\"> " +
            "                jobprofession = #{jobprofession}," +
            "            </if>" +
            "            <if test=\"null != phonenumber\"> " +
            "                phonenumber = #{phonenumber}," +
            "            </if>" +
            "        </set>" +
            "        where id = #{id}" +
            "</script>")
    public int update(Information information);

    //删除员工
    @Delete("DELETE FROM traffic.information where id=#{id}")
    public int del(Integer id);
}
